package models

import (
	"database/sql"
	"fmt"
	"log"
)

// Product 结构体表示商品信息
type Product struct {
	ID              int     `json:"id"`             // 商品ID
	ProductName     string  `json:"productname"`    // 商品名称
	ImageURL        string  `json:"image_url"`      // 图片URL
	Price           float64 `json:"price"`          // 价格
	Weight          string  `json:"weight"`         // 重量

	Type1           string  `json:"type1"`          // 总类
	Type2           string  `json:"type2"`          // 分类
	Date            string  `json:"date"`           // 生产日期
	FromPlace       string  `json:"fromplace"`      // 发货地
	Quantity        int     `json:"quantity"`       // 库存
	SalesVolume     int     `json:"salesvolume"`    // 销量
	Brand           string  `json:"brand"`          // 品牌
	Shop            string  `json:"shop"`           // 店家

	ReviewsNumber   int     `json:"reviewsnumber"`  // 评论数量
	ReviewsGrade    float64 `json:"reviewsgrade"`   // 评论分数
	Title           string  `json:"title"`          // 标题
}

// 配合initdata商品表添加新商品，若商品已存在则只更新数量
func AddProduct(p Product) (int64, error) {
	// 检查商品是否已存在
	existingProduct, err := GetProductByName(p.ProductName)
	if err != nil {
		log.Println("Error checking product:", err)
		return 0, err
	}

	if existingProduct.ProductName != "" {
		// 商品已存在，更新商品库存
		newQuantity := existingProduct.Quantity + p.Quantity
		stmt, err := db.Prepare(
			`UPDATE products SET 
                image_url = ?, price = ?, weight = ?,
                type1 = ?, type2 = ?, date = ?, fromplace = ?, quantity = ?, salesvolume = ?, brand = ?, shop = ?, 
                reviewsnumber = ?, reviewsgrade = ?, title = ? 
            WHERE id = ?`)
		if err != nil {
			log.Println("Error preparing statement for updating product quantity:", err)
			return 0, err
		}
		defer stmt.Close()

		// 执行更新操作
		_, err = stmt.Exec(
			p.ImageURL, p.Price, p.Weight,
			p.Type1, p.Type2, p.Date, p.FromPlace, newQuantity,
			p.SalesVolume, p.Brand, p.Shop,
			p.ReviewsNumber, p.ReviewsGrade, p.Title,
			existingProduct.ID)
		if err != nil {
			log.Println("Error executing update product quantity:", err)
			return 0, err
		}

		log.Printf("Product %s updated, new quantity: %d", p.ProductName, newQuantity)
		return int64(existingProduct.ID), nil
	}

	// 若商品不存在，插入新商品
	stmt, err := db.Prepare(
		`INSERT INTO products(
            productname, image_url, price, weight, 
            type1, type2, date, fromplace, quantity, salesvolume, brand, shop, reviewsnumber, reviewsgrade, title) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`)
	if err != nil {
		log.Println("Error preparing statement for AddProduct:", err)
		return 0, err
	}
	defer stmt.Close()

	// 插入商品信息，包括类型
	result, err := stmt.Exec(
		p.ProductName, p.ImageURL, p.Price, p.Weight,
		p.Type1, p.Type2, p.Date, p.FromPlace, p.Quantity, p.SalesVolume, p.Brand, p.Shop,
		p.ReviewsNumber, p.ReviewsGrade, p.Title)
	if err != nil {
		log.Println("Error executing AddProduct:", err)
		return 0, err
	}

	lastInsertID, err := result.LastInsertId()
	if err != nil {
		log.Println("Error getting last insert ID:", err)
		return 0, err
	}

	// log.Printf("New product %s added with type1: %s, type2: %s", p.ProductName, p.Type1, p.Type2)
	return lastInsertID, nil
}

// SearchProducts 搜索商品
func SearchProducts(keyword string) ([]Product, error) {
	// 使用 LIKE 进行模糊搜索，使用 % 作为通配符
	query := `
		SELECT id, productname, image_url, price, weight, type1, type2, date, 
			   fromplace, quantity, salesvolume, brand, shop, reviewsnumber, 
			   reviewsgrade, title
		FROM products 
		WHERE productname LIKE ? OR brand LIKE ? OR type1 LIKE ? OR type2 LIKE ?
	`
	// 在关键词前后添加 % 以实现模糊匹配
	searchPattern := "%" + keyword + "%"

	rows, err := db.Query(query, searchPattern, searchPattern, searchPattern, searchPattern)
	if err != nil {
		return nil, fmt.Errorf("搜索商品失败: %v", err)
	}
	defer rows.Close()

	var products []Product
	for rows.Next() {
		var product Product
		err := rows.Scan(
			&product.ID, &product.ProductName, &product.ImageURL, &product.Price,
			&product.Weight, &product.Type1, &product.Type2, &product.Date,
			&product.FromPlace, &product.Quantity, &product.SalesVolume,
			&product.Brand, &product.Shop, &product.ReviewsNumber,
			&product.ReviewsGrade, &product.Title,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描商品数据失败: %v", err)
		}
		products = append(products, product)
	}

	return products, nil
}

// 根据商品ID获取商品信息
func GetProductByID(id int) (Product, error) {
	var product Product
	err := db.QueryRow(
		`SELECT 
            id, productname, image_url, price, weight, 
            type1, type2, date, fromplace, quantity, salesvolume, brand, shop, reviewsnumber, reviewsgrade, title
        FROM products 
        WHERE id = ?`, id).
		Scan(
			&product.ID, &product.ProductName, &product.ImageURL, &product.Price, &product.Weight,
			&product.Type1, &product.Type2, &product.Date, &product.FromPlace, &product.Quantity, &product.SalesVolume, &product.Brand, &product.Shop,
			&product.ReviewsNumber, &product.ReviewsGrade, &product.Title,
		)
	if err != nil {
		if err == sql.ErrNoRows {
			return product, nil
		}
		return product, err
	}
	return product, nil
}

// 用是否同名检验商品是否已存在(有点局限)
func GetProductByName(productName string) (Product, error) {
	var product Product
	err := db.QueryRow(
		`SELECT 
            id, productname, image_url, price, weight, 
            type1, type2, date, fromplace, quantity, salesvolume, brand, shop, reviewsnumber, reviewsgrade, title
        FROM products 
        WHERE productname = ?`, productName).
		Scan(
			&product.ID, &product.ProductName, &product.ImageURL, &product.Price, &product.Weight,
			&product.Type1, &product.Type2, &product.Date, &product.FromPlace, &product.Quantity, &product.SalesVolume, &product.Brand, &product.Shop,
			&product.ReviewsNumber, &product.ReviewsGrade, &product.Title,
		)
	if err != nil {
		if err == sql.ErrNoRows {
			return product, nil
		}
		return product, err
	}
	return product, nil
}

// GetProductsByType 根据商品类型获取商品列表
func GetProductsByType(productType string) ([]Product, error) {
	var products []Product

	// 查询指定Type的所有商品
	rows, err := db.Query(
		`SELECT 
            id, productname, image_url, price, weight,
            type1, type2, date, fromplace, quantity, salesvolume, brand, shop,
            reviewsnumber, reviewsgrade, title
        FROM products 
        WHERE type1 = ? OR type2 = ?`, productType, productType)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	// 遍历查询结果
	for rows.Next() {
		var product Product
		err := rows.Scan(
			&product.ID, &product.ProductName, &product.ImageURL, &product.Price, &product.Weight,
			&product.Type1, &product.Type2, &product.Date, &product.FromPlace, &product.Quantity, &product.SalesVolume, &product.Brand, &product.Shop,
			&product.ReviewsNumber, &product.ReviewsGrade, &product.Title)
		if err != nil {
			return nil, err
		}
		products = append(products, product)
	}

	// 检查遍历过程中是否有错误
	if err = rows.Err(); err != nil {
		return nil, err
	}

	return products, nil
}

// GetAllProducts 获取所有商品
func GetAllProducts() ([]Product, error) {
	// 执行查询
	rows, err := db.Query(
		`SELECT 
            id, productname, image_url, price, weight, 
            type1, type2, date, fromplace, quantity, salesvolume, brand, shop, reviewsnumber, reviewsgrade, title
        FROM products 
        ORDER BY id`)
	if err != nil {
		return nil, fmt.Errorf("获取商品列表失败: %v", err)
	}
	defer rows.Close()

	// 用于存储结果的切片
	var products []Product

	// 遍历结果集
	for rows.Next() {
		var product Product
		err := rows.Scan(
			&product.ID, &product.ProductName, &product.ImageURL, &product.Price, &product.Weight,
			&product.Type1, &product.Type2, &product.Date, &product.FromPlace, &product.Quantity, &product.SalesVolume, &product.Brand, &product.Shop,
			&product.ReviewsNumber, &product.ReviewsGrade, &product.Title,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描商品数据失败: %v", err)
		}
		products = append(products, product)
	}

	// 检查遍历过程中是否有错误
	if err = rows.Err(); err != nil {
		return nil, fmt.Errorf("遍历商品数据时发生错误: %v", err)
	}

	return products, nil
}

// 删除商品
func DeleteProduct(id int) (int64, error) {
	stmt, err := db.Prepare("DELETE FROM products WHERE id = ?")
	if err != nil {
		log.Println("Error preparing statement for DeleteProduct:", err)
		return 0, err
	}
	defer stmt.Close()

	result, err := stmt.Exec(id)
	if err != nil {
		log.Println("Error executing DeleteProduct:", err)
		return 0, err
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		log.Println("Error getting rows affected for DeleteProduct:", err)
		return 0, err
	}

	log.Printf("Product with ID %d deleted, rows affected: %d", id, rowsAffected)
	return rowsAffected, nil
}
